"""
Copyright (c) 2022 Huawei Technologies Co.,Ltd.

openGauss is licensed under Mulan PSL v2.
You can use this software according to the terms and conditions of the Mulan PSL v2.
You may obtain a copy of Mulan PSL v2 at:

          http://license.coscl.org.cn/MulanPSL2

THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OF ANY KIND,
EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO NON-INFRINGEMENT,
MERCHANTABILITY OR FIT FOR A PARTICULAR PURPOSE.
See the Mulan PSL v2 for more details.
"""
"""
Case Type   : GSC功能模块
Case Name   : gsc开关打开，备机存在表缓存情况下进行switchover，新主执行ddl，dml功能正常
Create At   : 2023/5/19
Owner       : @peilinqian
Description :
    0、查询参数初始值;
    1、设置极GSC参数;
    2、重启数据库，使参数生效;
    3、创建多个数据库;
    4、执行批量建表删除表函数创建，批量建表;
    5、查询所有库并进行连接;
    6、主机对多个库所有表进行查询;
    7、备机对多个库所有表进行查询;
    8、备机执行switchover;
    9、新主机对多个库所有表进行查询;
    10、新备机对多个库所有表进行查询;
    11、备机执行switchover; expect:执行成功
    12、新主机对多个库所有表进行查询;
    13、新备机对多个库所有表进行查询;
Expect      :
    1、设置极GSC参数; expect:成功
    2、重启数据库，使参数生效; expect:重启成功
    3、创建多个数据库; expect:创建成功
    4、执行批量建表删除表函数创建，批量建表; expect:成功
    5、查询所有库并进行连接; expect:连接成功
    6、主机对多个库所有表进行查询; expect:执行成功
    7、备机对多个库所有表进行查询; expect:执行成功
    8、备机执行switchover; expect:执行成功
    9、新主机对多个库所有表进行查询; expect:执行成功
    10、新备机对多个库所有表进行查询; expect:执行成功
    11、备机执行switchover; expect:执行成功
    12、新主机对多个库所有表进行查询; expect:执行成功
    13、新备机对多个库所有表进行查询; expect:执行成功
History     :
"""
import os
import unittest

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node

pri_sh = CommonSH('PrimaryDbUser')
node_num = pri_sh.get_node_num()


@unittest.skipIf(node_num < 3, '非1主2备环境不执行')
class GscTestCase(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'----{os.path.basename(__file__)}:start----')
        self.log.info("----this is setup----")
        self.pri_root = Node(node='PrimaryRoot')
        self.sta_1_sh = CommonSH('Standby1DbUser')
        self.com = Common()
        self.constant = Constant()

        self.node_num = self.com.get_node_num(self.pri_root)
        self.table_num = 100
        self.db_num = 5
        self.fun_create_name = 'fun_create_tb_gsc0032'
        self.fun_drop_name = 'fun_drop_tb_gsc0032'
        self.db_ex_name = 'db_gsc0032_'
        self.tb_ex_name = 'tb_gsc0032_'
        self.fun_create_tb = f'''
        drop function if exists public.{self.fun_create_name};
        create or replace function public.{self.fun_create_name} (
        table_num integer)
        returns void
        language 'plpgsql'
        cost 100
        volatile 
        as \$body\$
        declare
        v_idx integer := 0;
        v_strtable varchar :='';
        v_strsql varchar :='';
        begin
          while v_idx < table_num loop
          v_idx = v_idx+1;
          v_strtable = concat('{self.tb_ex_name}', v_idx);
          v_strsql = 'create table '||v_strtable||'(idx integer,log varchar)';
          execute v_strsql;
          end loop;
         end
        \$body\$;'''

        self.fun_drop_tb = f'''
        drop function if exists  public.{self.fun_drop_name};
        create or replace function public.{self.fun_drop_name}(
        table_num_in integer)
        returns void
        language 'plpgsql'
        cost 100
        volatile 
        as \$body\$
        declare
        v_idx integer := 0;
        v_strtable varchar :='';
        v_strsql varchar :='';
        begin
          while v_idx < table_num_in loop
          v_idx = v_idx+1;
          v_strtable = concat('{self.tb_ex_name}', v_idx);
          v_strsql = 'drop table if exists '||v_strtable;
          execute v_strsql;
          end loop;
         end
        \$body\$;
        '''
        self.select_sql = ''
        for j in range(1, int(self.table_num) + 1):
            self.select_sql = self.select_sql + \
                              f'select * from {self.tb_ex_name}{j};'

    def test_main(self):
        step_txt = '----step0:查询参数初始值;----'
        self.log.info(step_txt)
        self.init_para1 = self.com.show_param('enable_global_syscache')
        self.init_para2 = self.com.show_param('global_syscache_threshold')

        step_txt = '----step1:设置极GSC参数; expect:成功----'
        self.log.info(step_txt)
        msg = pri_sh.execute_gsguc('set',
                                   self.constant.GSGUC_SUCCESS_MSG,
                                   f"enable_global_syscache= on")
        self.assertTrue(msg, '执行失败:' + step_txt)
        msg = pri_sh.execute_gsguc('set',
                                   self.constant.GSGUC_SUCCESS_MSG,
                                   'global_syscache_threshold=16MB')
        self.assertTrue(msg, '执行失败:' + step_txt)

        step_txt = '----step2:重启数据库，使参数生效; expect:重启成功----'
        self.log.info(step_txt)
        stop_result = pri_sh.stop_db_cluster()
        start_result = pri_sh.start_db_cluster()
        self.assertTrue(stop_result, '执行失败:' + step_txt)
        self.assertTrue(start_result, '执行失败:' + step_txt)

        step_txt = f'----step3:创建{self.db_num}个数据库; expect:创建成功----'
        self.log.info(step_txt)
        for i in range(self.db_num):
            create_db = f'create database {self.db_ex_name}{i};'
            create_result = pri_sh.execut_db_sql(create_db)
            self.log.info(create_result)
            self.assertIn(self.constant.CREATE_DATABASE_SUCCESS,
                          create_result, '执行失败:' + step_txt)

        step_txt = '----step4:执行批量建表删除表函数创建，批量建表; expect:成功----'
        self.log.info(step_txt)
        for i in range(self.db_num):
            result = pri_sh.execut_db_sql(self.fun_create_tb,
                                          dbname=f'{self.db_ex_name}{i}')
            self.log.info(result)
            self.assertIn(self.constant.CREATE_FUNCTION_SUCCESS_MSG,
                          result,
                          '执行失败:' + step_txt)
            result = pri_sh.execut_db_sql(self.fun_drop_tb,
                                          dbname=f'{self.db_ex_name}{i}')
            self.log.info(result)
            self.assertIn(self.constant.CREATE_FUNCTION_SUCCESS_MSG,
                          result,
                          '执行失败:' + step_txt)

            self.log.info(f"--{self.db_ex_name}{i}执行批量建表函数--")
            create_tb = f"select public.{self.fun_create_name} " \
                f"({self.table_num});"
            result = pri_sh.execut_db_sql(create_tb,
                                          dbname=f'{self.db_ex_name}{i}')
            self.log.info(result)
            check_tb = f"select count(*) from pg_class where relname " \
                f"like '{self.tb_ex_name}%';"
            result = pri_sh.execut_db_sql(check_tb,
                                          dbname=f'{self.db_ex_name}{i}')
            self.log.info(result)
            self.assertEqual(int(result.splitlines()[-2].strip()),
                             self.table_num, '执行失败:' + step_txt)

        step_txt = '----step5:查询所有库并进行连接; expect:连接成功----'
        self.log.info(step_txt)
        select_db_sql = "select datname from pg_database where datname " \
                        "not like('template%');"
        select_db_result = pri_sh.execut_db_sql(select_db_sql)
        self.log.info(select_db_result)
        db_list = select_db_result.splitlines()[2:-1:1]
        for i in db_list:
            con_result = pri_sh.execut_db_sql(
                'select current_database();',
                dbname=f'{i}')
            self.log.info(con_result)
            self.assertIn(i, con_result, '执行失败:' + step_txt)

        step_txt = f'----step6:主机对{self.db_num}个库所有表进行查询; expect:执行成功----'
        self.log.info(step_txt)
        for i in range(self.db_num):
            result = self.sta_1_sh.execut_db_sql(self.select_sql,
                                                 dbname=f'{self.db_ex_name}{i}')
            self.assertEqual(result.count('0 rows'), self.table_num,
                             '执行失败:' + step_txt)

        step_txt = f'----step7:备机对{self.db_num}个库所有表进行查询; expect:执行成功----'
        self.log.info(step_txt)
        for i in range(self.db_num):
            result = pri_sh.execut_db_sql(self.select_sql,
                                          dbname=f'{self.db_ex_name}{i}')
            self.assertEqual(result.count('0 rows'), self.table_num,
                             '执行失败:' + step_txt)

        step_txt = '----step8:备机执行switchover; expect:执行成功----'
        self.log.info(step_txt)
        success_msg = self.constant.SWITCH_SUCCESS_MSG
        switchover_result = self.sta_1_sh.execute_gsctl('switchover',
                                                        success_msg)
        self.assertTrue(switchover_result, '执行失败:' + step_txt)
        refreshconf_result = self.sta_1_sh.exec_refresh_conf()
        self.assertTrue(refreshconf_result, '执行失败:' + step_txt)
        self.log.info('----查看主机query，同步是否正常----')
        self.sta_1_sh.check_location_consistency('primary',
                                                 self.node_num)
        self.log.info('----查询主备状态----')
        status_result = pri_sh.get_db_cluster_status('status')
        self.assertTrue(status_result, '执行失败:' + step_txt)

        step_txt = f'----step9:新主机对{self.db_num}个库所有表进行查询; expect:执行成功----'
        self.log.info(step_txt)
        for i in range(self.db_num):
            result = self.sta_1_sh.execut_db_sql(self.select_sql,
                                                 dbname=f'{self.db_ex_name}{i}')
            self.assertEqual(result.count('0 rows'), self.table_num,
                             '执行失败:' + step_txt)

        step_txt = f'----step10:新备机对{self.db_num}个库所有表进行查询; expect:执行成功----'
        self.log.info(step_txt)
        for i in range(self.db_num):
            result = pri_sh.execut_db_sql(self.select_sql,
                                          dbname=f'{self.db_ex_name}{i}')
            self.assertEqual(result.count('0 rows'), self.table_num,
                             '执行失败:' + step_txt)

        step_txt = '----step11:备机执行switchover; expect:执行成功----'
        self.log.info(step_txt)
        success_msg = self.constant.SWITCH_SUCCESS_MSG
        switchover_result = pri_sh.execute_gsctl('switchover',
                                                 success_msg)
        self.assertTrue(switchover_result, '执行失败:' + step_txt)
        refreshconf_result = pri_sh.exec_refresh_conf()
        self.assertTrue(refreshconf_result, '执行失败:' + step_txt)
        self.log.info('----查看主机query，同步是否正常----')
        pri_sh.check_location_consistency('primary',
                                          self.node_num)
        self.log.info('----查询主备状态----')
        status_result = pri_sh.get_db_cluster_status('status')
        self.assertTrue(status_result, '执行失败:' + step_txt)

        step_txt = f'----step12:新主机对{self.db_num}个库所有表进行查询; expect:执行成功----'
        self.log.info(step_txt)
        for i in range(self.db_num):
            result = pri_sh.execut_db_sql(self.select_sql,
                                          dbname=f'{self.db_ex_name}{i}')
            self.assertEqual(result.count('0 rows'), self.table_num,
                             '执行失败:' + step_txt)

        step_txt = f'----step13:新备机对{self.db_num}个库所有表进行查询; expect:执行成功----'
        self.log.info(step_txt)
        for i in range(self.db_num):
            result = self.sta_1_sh.execut_db_sql(self.select_sql,
                                                 dbname=f'{self.db_ex_name}{i}')
            self.assertEqual(result.count('0 rows'), self.table_num,
                             '执行失败:' + step_txt)

    def tearDown(self):
        step_txt = '----this is teardown----'
        self.log.info(step_txt)
        host_name = self.pri_root.sh('hostname').result()
        if pri_sh.check_whether_need_switch(host_name):
            step_txt = '----主备关系异常，进行恢复----'
            self.log.info(step_txt)
            self.log.info('---进行主备切换---')
            success_msg = self.constant.SWITCHOVER_SUCCESS_MSG
            switchover_result = pri_sh.execute_gsctl('switchover',
                                                     success_msg)
            self.log.info(switchover_result)
            refreshconf_result = pri_sh.exec_refresh_conf()
            self.log.info(refreshconf_result)

        step1_txt = '----删除数据库 expect:删除成功----'
        self.log.info(step1_txt)
        drop_db_msg = ''
        for i in range(self.db_num):
            drop_db = f'drop database {self.db_ex_name}{i}'
            drop_db_msg = drop_db_msg + pri_sh.execut_db_sql(drop_db)

        step2_txt = '----恢复参数为初始值并查询; expect:设置成功----'
        self.log.info(step2_txt)
        msg1 = pri_sh.execute_gsguc('set',
                                    self.constant.GSGUC_SUCCESS_MSG,
                                    f"enable_global_syscache="
                                    f"{self.init_para1}")
        msg2 = pri_sh.execute_gsguc('set',
                                    self.constant.GSGUC_SUCCESS_MSG,
                                    f"global_syscache_threshold="
                                    f"{self.init_para2}")
        step3_txt = '----重启数据库，使参数生效; expect:重启成功----'
        self.log.info(step3_txt)
        restart_result = pri_sh.restart_db_cluster()
        step4_txt = '----查询数据库状态; expect:状态正常----'
        self.log.info(step4_txt)
        status_result = pri_sh.get_db_cluster_status('status')

        flag = drop_db_msg.count(self.constant.DROP_DATABASE_SUCCESS)
        self.assertEqual(flag, self.db_num, '执行失败:' + step_txt + step1_txt)
        self.assertTrue(msg1, '执行失败:' + step_txt + step2_txt)
        self.assertTrue(msg2, '执行失败:' + step_txt + step2_txt)
        self.assertTrue(restart_result, '执行失败:' + step_txt + step3_txt)
        self.assertTrue(status_result, '执行失败:' + step_txt + step4_txt)
        self.log.info(f'----{os.path.basename(__file__)}:end----')
